package DBconnection;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSetMetaData;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.Vector;

import com.mysql.jdbc.Blob;

import Common.*;

/**
 * 
 * @author all
 * This class handle the write and read from the database
 *
 */
public class mysqlConnection {

	
/** the connection to the database*/
	private Connection con;
	/**url to the database location at the server side*/
	private static final String url="jdbc:mysql://localhost/test";//only the user table
	/**hold the name of the mysql database driver*/
	private static final String className="com.mysql.jdbc.Driver";	
	/**database user name*/
	private static final String user="root";
	/** database password*/
	private static final String pass="Braude";//temp password the local server
	
	/**
	 * Initiate the connector and create connection to the database
	 * @throws Exception 
	 * @throws Exception
	 */
	
	public mysqlConnection () throws Exception 
	{
		try
		{
			Class.forName(className).newInstance();
		}
		catch (Exception ex)
		{
			throw new Exception("Couldn't connect to the database: " + ex.getMessage());
		}	
		
		try
		{
		   con = DriverManager.getConnection(url, user, pass);
		   System.out.println("SQL connection succeed");
		}
		catch (Exception ex)
		{
			throw new Exception("Couldn't connect to the database: " + ex.getMessage());
		}	
	}
	
	
	
	
	
	/**
	 * 
	 * @param msg that hold ids of the students
	 * @return vector of courses to each student 
	 * @throws SQLException
	 */
	public Object Uquery(String[] msg) throws SQLException


	{
		 ResultSet rs=null;
		 int i;
		   
		   PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT Cid FROM studentcourses WHERE sid=?;");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		 
			try {
				for(i=0;i<msg.length;i++)
				{
				ps.setString(i+1, msg[i]);
				}
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
	
		   try {
			rs=ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		   ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			String[] headlines=new String[numOfCols];
			for (int j=1; j<=numOfCols; j++)
				try {
					headlines[j-1] = rsmd.getColumnName(j);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			result.addElement(headlines);
			
			try {
				while (rs.next()){
					String[] tuple=new String[numOfCols];
					for ( i=1 ; i<=numOfCols ; i++)
						tuple[i-1] = rs.getString(i);
					result.addElement(tuple);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
				return result;
		
		}
	/**
	 * attribute the class to the course with chosen teacher,attribute the students to course
	 * @param msg that hold classid,teacherid,and student ids
	 * @return
	 */
	public Object UpdateCourseToStudent(String[] msg)
	{
		PreparedStatement ps=null;
		String Cid=msg[1];
		String ClassID=msg[msg.length-1];
		String TeacherId=msg[msg.length-2];
		String[] Sids=new String[msg.length-4];
		
		
		
		for(int i=0;i<Sids.length;i++)
		{
			Sids[i]=msg[i+2];
		}
		
		
		int res=0;
		for(int i=0;i<Sids.length;i++)
		{
			try {
				ps = con.prepareStatement("INSERT INTO test.studentcourses(Sid,Cid,Year,Semester,Cname) Values(?,?,(SELECT Year FROM test.semesters WHERE CloseToEdit=false AND Current=true),(SELECT Semester FROM test.semesters WHERE CloseToEdit=false AND Current=true ),(SELECT CourseName FROM test.courses WHERE Cid=?));");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
			
				ps.setString(1, Sids[i]);
				ps.setString(2, Cid);
				ps.setString(3, Cid);
					
			
				res= ps.executeUpdate();
		
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
		}

		try {
			ps = con.prepareStatement("INSERT INTO test.classescourses (ClassID,Cid,TeacherID,Year,Semester) VALUES (?,?,?,(SELECT Year FROM test.semesters WHERE CloseToEdit=false AND Current=true),(SELECT Semester FROM test.semesters WHERE CloseToEdit=false AND Current=true ));");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
		
			ps.setString(1, ClassID);
			ps.setString(2, Cid);
			ps.setString(3, TeacherId);
				
		
			res= ps.executeUpdate();
	
		
	
	}
	catch (SQLException ex){
		System.out.println("\n in CourseToStudentQuery error ");
		return ex;}

		try {
			ps = con.prepareStatement("UPDATE test.teachers SET counth=counth+(SELECT c.StudHours FROM test.courses c WHERE c.Cid=?) WHERE Tid=?;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
		
			
			ps.setString(1, Cid);
			ps.setString(2, TeacherId);
				
		
			res= ps.executeUpdate();
	
		
	
	}
	catch (SQLException ex){
		System.out.println("\n in CourseToStudentQuery error ");
		return ex;}	
		Vector<String[]> result = new Vector<String[]>();
		String[] identity=new String[1];
		identity[0]="AttributeStudentsToCourse";
		result.add(identity);
		
		return result;
	}
	/**
	 * lock current semester to update
	 * @return aprove to the lock of the current semester
	 */
	public Object UpdateLockToCurrentSemester()
	{
		PreparedStatement ps=null;
		
		
		int res=0;
		
			try {
				ps = con.prepareStatement("UPDATE test.semesters SET CloseToEdit=true WHERE (Current=true);");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
			
			
				res= ps.executeUpdate();
		
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
			Vector<String[]> result = new Vector<String[]>();
			String[] identity=new String[1];
			identity[0]="UpdateLockToCurrentSemester";
			result.add(identity);

		return result;
	}
	/**
	 * create exceptional request of student add to the course the studies in chosen class
	 * @param msg that hold the course id, student id, class id
	 * @return
	 */
	public Object CreateExReqAddStudToCourse(String[] msg)
	{
		PreparedStatement ps=null;
		String Cid=msg[1];
		String Sid=msg[2];
		String ClassId=msg[3];
		
		int res=0;
		
			try {
				ps = con.prepareStatement("INSERT INTO test.exreqstudadd (Sid,Cid,ClassId)VALUES(?,?,?);");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				ps.setString(1, Sid);
				ps.setString(2, Cid);
				ps.setString(3,ClassId);
				res= ps.executeUpdate();
		
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in CreateExReqAddStudToCourse error ");
			return ex;}
			Vector<String[]> result = new Vector<String[]>();
			String[] identity=new String[1];
			identity[0]="CreateExReqAddStudToCourse";
			result.add(identity);

		return result;
	}
	/**
	 * set the decision of the principle on request of exceptional request to add student to class
	 * @param msg that contains the request id
	 * @return
	 */
	public Object SetDesExReqAddStudToCourse(String[] msg)
	{
		PreparedStatement ps=null;
		ResultSet rs=null;
		Vector<String[]> result = new Vector<String[]>();
		String Sid;
		String Cid;
		String ClassId;
		String Year;
		String Semester;
		int Aprove;
		String Rid=msg[1];
		if(msg[2].equalsIgnoreCase("true"))  Aprove=1;
		else Aprove=0;
		
		int res=0;
		
			try {
				ps = con.prepareStatement("UPDATE exreqstudadd SET Aprove=?,Handled=true WHERE Rid=?;");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				
				ps.setInt(1, Aprove);
				ps.setString(2, Rid);
			
				res= ps.executeUpdate();
		
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in SetDesExReqAddStudToCourse error ");
			return ex;}
			if(Aprove==1)
			{
					try {
						ps = con.prepareStatement("SELECT e.Sid,e.Cid,e.ClassId,c.Year,c.Semester FROM exreqstudadd e,classescourses c WHERE e.Cid=c.Cid AND e.ClassId=c.ClassID AND e.Rid=?;");
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					try {
						
						
						ps.setString(1, Rid);
					
						rs= ps.executeQuery();
						ResultSetMetaData rsmd = rs.getMetaData();
						int numOfCols = rsmd.getColumnCount();
						
						while (rs.next()){
							String[] tuple=new String[numOfCols];
							for (int i=1 ; i<=numOfCols ; i++)
								tuple[i-1] = rs.getString(i);
							result.addElement(tuple);
							
						}
						rs.close();
						Sid=result.get(0)[0];
						Cid=result.get(0)[1];
						ClassId=result.get(0)[2];
						Year=result.get(0)[3];
						Semester=result.get(0)[4];
				
					
				
				}
				catch (SQLException ex){
					System.out.println("\n in SetDesExReqAddStudToCourse error ");
					return ex;}
					
					try {
						ps = con.prepareStatement("INSERT INTO studcoursesexcep VALUES (?,?,?,?,?)");
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					try {
						
						ps.setString(1, Sid);
						ps.setString(2, Cid);
						ps.setString(3, ClassId);
						ps.setString(4, Year);
						ps.setString(5, Semester);
					
						res= ps.executeUpdate();
				
					
				
				}
				catch (SQLException ex){
					System.out.println("\n in SetDesExReqAddStudToCourse error ");
					return ex;}
					result = new Vector<String[]>();
					String[] identity=new String[1];
					identity[0]="SetDesExReqAddStudToCourse";
					result.add(identity);
		
				return result;
			}
			else
			{
				String[] identity=new String[1];
				identity[0]="AproveExReqAddStudToCourse";
				result.add(identity);
				return result;
			}
	}
	public Object SetDesExReqRemovalStudToCourse(String[] msg)
	{
		PreparedStatement ps=null;
		ResultSet rs=null;
		Vector<String[]> result = new Vector<String[]>();
		
		int Aprove;
		String Rid=msg[1];
		if(msg[2].equalsIgnoreCase("true"))  Aprove=1;
		else Aprove=0;
		
		int res=0;
		
			try {
				ps = con.prepareStatement("UPDATE exreqstudrem SET Aprove=?,Handled=true WHERE Rid=?;");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				
				ps.setInt(1, Aprove);
				ps.setString(2, Rid);
			
				res= ps.executeUpdate();
		
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in SetDesExReqRemovalStudToCourse error ");
			return ex;}
			if(Aprove==1)
			{
					try {
						ps = con.prepareStatement("DELETE  FROM studentcourses WHERE ((Sid=(SELECT  e.Sid FROM exreqstudrem e WHERE e.Rid=?)) AND (Cid=(SELECT e.Cid FROM exreqstudrem e WHERE e.Rid=?)) AND (Year=(SELECT a.Year FROM semesters a WHERE a.Current=true)) AND (Semester=(SELECT a.Semester FROM semesters a WHERE a.Current=true)));");
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					try {
						
						
						ps.setString(1, Rid);
						ps.setString(2, Rid);
						
						res= ps.executeUpdate();
						String[] identity=new String[1];
						identity[0]="SetDesExReqRemovalStudToCourse";
						result.add(identity);
						
						return result;
					
				
				}
				catch (SQLException ex){
					System.out.println("\n in SetDesExReqRemovalStudToCourse error ");
					return ex;}
					
			
			}
			else
			{
				String[] identity=new String[1];
				identity[0]="SetDesExReqRemovalStudToCourse";
				result.add(identity);
				return result;
			}
	}
	public Object SetDesExReqReplaceTeacher(String[] msg)
	{
		PreparedStatement ps=null;
		ResultSet rs=null;
		Vector<String[]> result = new Vector<String[]>();
		
		int Aprove;
		String Rid=msg[1];
		if(msg[2].equalsIgnoreCase("true"))  Aprove=1;
		else Aprove=0;
		
		int res=0;
		
			try {
				ps = con.prepareStatement("UPDATE exreqteacherrep SET Aprove=?,Handled=true WHERE Rid=?;");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				
				ps.setInt(1, Aprove);
				ps.setString(2, Rid);
			
				res= ps.executeUpdate();
		
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in SetDesExReqReplaceTeacher error ");
			return ex;}
			if(Aprove==1)
			{
					try {
						ps = con.prepareStatement("UPDATE classescourses SET TeacherID=(SELECT repTid FROM exreqteacherrep WHERE Rid=?);");
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					try {
						
						
						ps.setString(1, Rid);
						
						
						res= ps.executeUpdate();

						try {
							ps = con.prepareStatement("UPDATE teachers SET counth=counth-(SELECT StudHours FROM courses WHERE Cid=(SELECT Cid FROM exreqteacherrep WHERE Rid=? )) WHERE Tid=(SELECT Tid FROM exreqteacherrep WHERE Rid=?);");
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						
						ps.setString(1, Rid);
						ps.setString(2, Rid);
						res= ps.executeUpdate();
						
						try {
							ps = con.prepareStatement("UPDATE teachers SET counth=counth+(SELECT StudHours FROM courses WHERE Cid=(SELECT Cid FROM exreqteacherrep WHERE Rid=? )) WHERE Tid=(SELECT repTid FROM exreqteacherrep WHERE Rid=?);");
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
						
						ps.setString(1, Rid);
						ps.setString(2, Rid);
						res= ps.executeUpdate();
						
						
						String[] identity=new String[1];
						identity[0]="SetDesExReqReplaceTeacher";
						result.add(identity);
						
						return result;
					
				
				}
				catch (SQLException ex){
					System.out.println("\n in SetDesExReqReplaceTeacher error ");
					return ex;}
					
			
			}
			else
			{
				String[] identity=new String[1];
				identity[0]="SetDesExReqReplaceTeacher";
				result.add(identity);
				return result;
			}
	}
	public Object InsertStudentToClass(String[] msg)
	{
		PreparedStatement ps=null;
		
		String ClassId=msg[1];
		String Sid=msg[2];
		
		int res=0;
		
			try {
				ps = con.prepareStatement("UPDATE studentdb SET ClassID=? WHERE sid=?;");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				ps.setString(1, ClassId);
				ps.setString(2, Sid);
				
				res= ps.executeUpdate();
		
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in InsertStudentToClass error ");
			return ex;}
			Vector<String[]> result = new Vector<String[]>();
			String[] identity=new String[1];
			identity[0]="InsertStudentToClass";
			result.add(identity);

		return result;
	}
	public Object DataForHistBetweenTeachersInChosenClass(String[] msg)
	{
		PreparedStatement ps=null;
		String ClassID=msg[1];
		ResultSet rs=null;
		Vector<String[]> result = new Vector<String[]>();
		
		
			try {
				ps = con.prepareStatement("SELECT t.TFname,t.TLname, AVG(sc.Grade) as avgGrade FROM studentcourses sc,studentdb sd,classescourses cc,teachers t WHERE (sc.Sid=sd.Sid) AND (sc.Cid=cc.Cid) AND (cc.TeacherID=t.Tid) AND (sd.ClassID=cc.ClassID) AND (cc.ClassID=?) GROUP BY t.Tid;");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				ps.setString(1, ClassID);
				rs= ps.executeQuery();
				ResultSetMetaData rsmd = rs.getMetaData();
				int numOfCols = rsmd.getColumnCount();
				
				while (rs.next()){
					String[] tuple=new String[numOfCols];
					for (int i=1 ; i<=numOfCols ; i++)
						tuple[i-1] = rs.getString(i);
					result.addElement(tuple);
					
				}
				rs.close();
				String[] identity=new String[numOfCols];
				identity[0]="DataForHistBetweenTeachersInChosenClass";
				result.add(identity);
				
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
			
			

		return result;
	}
	public Object DataForHistBetweenClassesToChosenTeacher(String[] msg)
	{
		PreparedStatement ps=null;
		String TeacherID=msg[1];
		ResultSet rs=null;
		Vector<String[]> result = new Vector<String[]>();
		
		
			try {
				ps = con.prepareStatement("SELECT cc.ClassID, AVG(sc.Grade) as avgGrade FROM studentcourses sc,studentdb sd,classescourses cc,teachers t WHERE (sc.Sid=sd.Sid) AND (sc.Cid=cc.Cid) AND (cc.TeacherID=t.Tid) AND (sd.ClassID=cc.ClassID) AND (cc.TeacherID=?) GROUP BY cc.ClassID;");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				ps.setString(1, TeacherID);
				rs= ps.executeQuery();
				ResultSetMetaData rsmd = rs.getMetaData();
				int numOfCols = rsmd.getColumnCount();
				
				while (rs.next()){
					String[] tuple=new String[numOfCols];
					for (int i=1 ; i<=numOfCols ; i++)
						tuple[i-1] = rs.getString(i);
					result.addElement(tuple);
					
				}
				rs.close();
				String[] identity=new String[numOfCols];
				identity[0]="DataForHistBetweenClassesToChosenTeacher";
				result.add(identity);
				
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in DataForHistBetweenClassesToChosenTeacher error ");
			return ex;}
			
			

		return result;
	}
	public Object DataForHistBetweenCoursesToChosenClass(String[] msg)
	{
		PreparedStatement ps=null;
		String ClassID=msg[1];
		ResultSet rs=null;
		Vector<String[]> result = new Vector<String[]>();
		
		
			try {
				ps = con.prepareStatement("SELECT sc.Cname, AVG(sc.Grade) as avgGrade FROM studentcourses sc,studentdb sd,classescourses cc WHERE (sc.Sid=sd.Sid) AND (sc.Cid=cc.Cid) AND (sd.ClassID=cc.ClassID) AND (cc.ClassID=?) GROUP BY cc.Cid;");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				ps.setString(1, ClassID);
				rs= ps.executeQuery();
				ResultSetMetaData rsmd = rs.getMetaData();
				int numOfCols = rsmd.getColumnCount();
				
				while (rs.next()){
					String[] tuple=new String[numOfCols];
					for (int i=1 ; i<=numOfCols ; i++)
						tuple[i-1] = rs.getString(i);
					result.addElement(tuple);
					
				}
				rs.close();
				String[] identity=new String[numOfCols];
				identity[0]="DataForHistBetweenCoursesToChosenClass";
				result.add(identity);
				
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in DataForHistBetweenCoursesToChosenClass error ");
			return ex;}
			
			

		return result;
	}
	public Object CreateExReqRemoveStudFromCourse(String[] msg)
	{
		PreparedStatement ps=null;
		String Cid=msg[1];
		String Sid=msg[2];
		
		int res=0;
		
			try {
				ps = con.prepareStatement("INSERT INTO test.exreqstudrem (Sid,Cid)VALUES(?,?);");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				ps.setString(1, Sid);
				ps.setString(2, Cid);
				
				res= ps.executeUpdate();
		
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
			Vector<String[]> result = new Vector<String[]>();
			String[] identity=new String[1];
			identity[0]="CreateExReqRemoveStudFromCourse";
			result.add(identity);

		return result;
	}
	public Object CreateExReqTeacherReplacement(String[] msg)
	{
		PreparedStatement ps=null;
		String Tid=msg[1];
		String ClassId=msg[2];
		String Cid=msg[3];
		String RepTid=msg[4];
		
		int res=0;
		
			try {
				ps = con.prepareStatement("INSERT INTO test.exreqteacherrep (Tid,ClassID,Cid,repTid)VALUES(?,?,?,?);");
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				ps.setString(1, Tid);
				ps.setString(2, ClassId);
				ps.setString(3, Cid);
				ps.setString(4, RepTid);
				
				res= ps.executeUpdate();
		
			
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
			Vector<String[]> result = new Vector<String[]>();
			String[] identity=new String[1];
			identity[0]="CreateExReqTeacherReplacement";
			result.add(identity);

		return result;
	}
	public Object CoursesOfStudOfClassQuery()
	{
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		System.out.println("\n done with sql ");
		try {
			ps = con.prepareStatement("SELECT s.Cname,s.Cid,s.Semester,s.Year,s.Grade,s.Sid,st.FirstName,st.LastName,st.ClassID FROM studentcourses s,studentdb st WHERE s.Sid=st.Sid AND (s.Grade is not null) ;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
		
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			
			String[] identity=new String[numOfCols];
			if (isempty)
			{
				
				identity[1]="empty";
				result.addElement(identity);
			}
			
			identity[0]="CoursesOfStudOfClassQuery";
			result.addElement(identity);
			
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
	}
	public Object CoursesOfClassQuery()
	{
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		System.out.println("\n done with sql ");
		try {
			ps = con.prepareStatement("SELECT c.ClassID,c.Cid FROM classescourses c,semesters s WHERE (c.Year=s.Year)AND(c.Semester=s.Semester)AND(s.Current=false);");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
		
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			
			String[] identity=new String[numOfCols];
			if (isempty)
			{
				
				identity[1]="empty";
				result.addElement(identity);
			}
			
			identity[0]="CoursesOfClassQuery";
			result.addElement(identity);
			
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
	}
	
	public Object PreCoursesQuery()
	{
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT * FROM precourses;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
		
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			
			String[] identity=new String[numOfCols];
			if (isempty)
			{
				
				identity[1]="empty";
				result.addElement(identity);
			}
			
			identity[0]="CoursesWithPreCoursesQuery";
			result.addElement(identity);
			
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
	}
	
	public Object ParentQueryForManagment(String[] msg)
	{
		
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		int BlockWantedStatus;
		
		if(msg[1].equalsIgnoreCase("blocked")) BlockWantedStatus=1;
		else BlockWantedStatus=0;
		
		try {
			ps = con.prepareStatement("SELECT * FROM parents p WHERE Blocked=? ;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.setInt(1, BlockWantedStatus);
			rs = ps.executeQuery();
		
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			
			String[] identity=new String[numOfCols];
		
			
			identity[0]="ParentQueryForManagment";
			result.addElement(identity);
		
		
			
			System.out.println("\n done with sql result size is "+result.size() );
			return result;
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
	}
	public Object ParentBlockForManagment(String[] msg)
	{
		
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		int BlockWantedStatus;
		
		if(msg[1].equalsIgnoreCase("block")) BlockWantedStatus=1;
		else BlockWantedStatus=0;
		String pid=msg[2];
		try {
			ps = con.prepareStatement("UPDATE parents SET Blocked=? WHERE Pid=?;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.setInt(1, BlockWantedStatus);
			ps.setString(2, pid);
			
			ps.executeUpdate();
		
			Vector<String[]> result = new Vector<String[]>();
		
			String[] identity=new String[1];
	
			identity[0]="ParentBlockForManagment";
			result.addElement(identity);
		
			return result;
		
		}
		catch (SQLException ex){
			System.out.println("\n in ParentBlockForManagment error ");
			return ex;}
	}
	public Object CoursesOfClassesToAdmin()
	{
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT A.Cid,A.CourseName,A.StudUnit,A.StudHours,B.ClassID,B.TeacherID,B.Year,B.Semester FROM courses A,classescourses B WHERE A.Cid=B.Cid;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
		
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			
			String[] identity=new String[numOfCols];
			if (isempty)
			{
				
				identity[1]="empty";
				result.addElement(identity);
			}
			
			identity[0]="CoursesOfClassesAdminQuery";
			result.addElement(identity);
			
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
	}
	
	public Object CoursesToAdmin()
	{
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT a.Cid,a.CourseName,a.StudUnit,a.StudHours FROM courses a;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
		
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			
			String[] identity=new String[numOfCols];
			if (isempty)
			{
				
				identity[1]="empty";
				result.addElement(identity);
			}
			
			identity[0]="CoursesAdminQuery";
			result.addElement(identity);
			
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
	}
	public Object CoursesAdminForExQuery()
	{
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT a.Cid,a.CourseName,a.StudUnit,a.StudHours, b.ClassID FROM courses a,classescourses b WHERE a.Cid=b.Cid;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
		
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			
			String[] identity=new String[numOfCols];
			if (isempty)
			{
				
				identity[1]="empty";
				result.addElement(identity);
			}
			
			identity[0]="CoursesAdminForExQuery";
			result.addElement(identity);
			
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CoursesAdminForExQuery error ");
			return ex;}
	}
	public Object UnattributedStudQuery()
	{
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT sid,FirstName,LastName FROM studentdb where ClassID is null;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
		
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			String[] headlines=new String[numOfCols];
			for (int j=1; j<=numOfCols; j++)
				headlines[j-1] = rsmd.getColumnName(j);
			result.addElement(headlines);
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			//if (isempty)System.out.println("\n sql : Is empty ");
			String[] identity=new String[numOfCols];
			if (isempty)
			{
				
				identity[1]="NoUnattributedStudents";
				result.addElement(identity);
			}
			
			identity[0]="StudUnatQuery";
			result.addElement(identity);
			
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
	}
	public Object ClassesQuery()
	{
		ResultSet rs=null;
		
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT * FROM classes;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			String[] headlines=new String[numOfCols];
			for (int j=1; j<=numOfCols; j++)
				headlines[j-1] = rsmd.getColumnName(j);
			result.addElement(headlines);
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			
			identity[0]="ClassesQueryForAdmin";
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
	}
	public Object StudentsExQuery()
	{
		ResultSet rs=null;
		
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT * FROM studentdb;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			
			identity[0]="StudentQueryForEx";
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
		
	}
	public Object StudentCoursesRemoveExQuery()
	{
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT Cname,Cid,Semester,Year,Grade,Sid FROM studentcourses WHERE Year=(SELECT s.Year FROM semesters s WHERE s.Current=true) AND Semester=(SELECT s.Semester FROM semesters s WHERE s.Current=true); ");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			String[] identity=new String[numOfCols];
			
			identity[0]="StudentCoursesRemoveExQuery";
			
			
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in StudentCoursesRemoveExQuery error ");
			return ex;}
		
	}
	public Object StudentCoursesAddExQuery()
	{
		ResultSet rs=null;
		boolean isempty=true;
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT Cname,Cid,Semester,Year,Grade,Sid FROM studentcourses WHERE Grade is not null;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				isempty=false;
			}
			rs.close();
			String[] identity=new String[numOfCols];
			
			identity[0]="StudentCoursesAddExQuery";
			
			
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in StudentCoursesAddExQuery error ");
			return ex;}
		
	}
	public Object PersonalDataQuery(String[] msg)
	{

		ResultSet rs=null;
		
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT Sid,FirstName,LastName,ClassID FROM studentdb WHERE sid=?;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.setString(1, msg[1]);
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			String[] headlines=new String[numOfCols];
			for (int j=1; j<=numOfCols; j++)
				headlines[j-1] = rsmd.getColumnName(j);
			result.addElement(headlines);
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			if(msg[0].startsWith("PersonalDataForParent")) identity[0]="PersonalDataForParent";
			else if (msg[0].startsWith("PersonalDataForPrinciple"))identity[0]="PersonalDataForPrinciple";
			else identity[0]="PersonalDataForStudent";
			result.addElement(identity);
		//	CoursesResult.lOfCourses=new Vector<Course>();
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
		}
	public Object TeachersQuery ()
	{
		ResultSet rs=null;
		
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT a.Tid,a.TFname,a.TLname,a.maxh,a.counth,b.studUId FROM teachers a,teacherstudyunit b WHERE a.Tid=B.Tid ;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			
			identity[0]="TeachersQuery";
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in TeachersQuery error ");
			return ex;}
	}
	public Object TeachersForHistQuery ()
	{
		ResultSet rs=null;
		
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT a.Tid,a.TFname,a.TLname,a.maxh,a.counth FROM teachers a;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			
			identity[0]="TeachersForHistQuery";
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;}
	}
	public Object ExReqTeacherRepQuery ()
	{
		ResultSet rs=null;
		
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT e.Rid,e.Tid,t1.TFname,t1.TLname,e.ClassID,e.Cid,c.CourseName,e.repTid,t2.TFname,t2.TLname FROM exreqteacherrep e,courses c,teachers t1,teachers t2 WHERE e.Tid=t1.Tid AND e.repTid=t2.Tid AND e.Cid=c.Cid AND e.Handled=false;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			
			identity[0]="ExReqTeacherRepQuery";
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in ExReqTeacherRepQuery error ");
			return ex;}
	}
	
	public Object ExReqStudAddQuery ()
	{
		ResultSet rs=null;
		
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT e.Rid, e.Sid, s.FirstName, s.LastName, e.Cid,c.CourseName,s.ClassID FROM exreqstudadd e,studentdb s,courses c WHERE e.Sid=s.Sid AND e.Cid=c.Cid AND e.Handled=false;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			
			identity[0]="ExReqStudAddQuery";
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in ExReqStudAddQuery error ");
			return ex;}
	}
	public Object ExReqStudRemQuery ()
	{
		ResultSet rs=null;
		
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT e.Rid, e.Sid, s.FirstName, s.LastName, e.Cid, c.CourseName FROM exreqstudrem e,studentdb s,courses c WHERE e.Sid=s.Sid AND e.Cid=c.Cid AND e.Handled=false; ");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			
			identity[0]="ExReqStudRemQuery";
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in ExReqStudRemQuery error ");
			return ex;}
	}
	
	
	
	public String[] YearQuery (String[] msg)
	
	{
		ResultSet rs=null;
		String[]result=new String[4];
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT StartYear,EndYear FROM studentdb WHERE sid=?;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.setString(1, msg[1]);
			rs=ps.executeQuery();
			while(rs.next())
			{
				result[1]=rs.getString(1);
				result[2]=rs.getString(2);
			}
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		
		}
		result[0]="YearQuery";
		result[3]="YearQuery";
		System.out.println("\n1  in get year");
		return result;
	}
	
	public Object ChildQuery(String[] msg)
	{
		ResultSet rs=null;
		
		PreparedStatement ps=null;

		try {
			ps = con.prepareStatement("SELECT Sid  FROM parentstudent WHERE Pid=?;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.setString(1, msg[1]);
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			String[] headlines=new String[numOfCols];
			for (int j=1; j<=numOfCols; j++)
				headlines[j-1] = rsmd.getColumnName(j);
			result.addElement(headlines);
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			identity[0]="ChildrenOfParent";
			result.addElement(identity);
		//	CoursesResult.lOfCourses=new Vector<Course>();
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;
		}
		
	}
	public Object CreateNewClass(String[] msg)
	{
		ResultSet rs=null;
		Vector<String[]> result = new Vector<String[]>();
		PreparedStatement ps=null;
		String grade=msg[1];
		
		String nextletter="";

		try {
			ps = con.prepareStatement("SELECT max(numlet) FROM classesgrades WHERE Grade=?;");
		
			ps.setString(1, grade);
			rs = ps.executeQuery();
			
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);
				
			}
			rs.close();
			
			
			if(result.get(0)[0]!=null)
			{
				 nextletter=result.get(0)[0];
				char[] temp=nextletter.toCharArray();
				char let=temp[0];
				let++;
				Character temp2=new Character(let);
				nextletter=temp2.toString();
			
			}
			else 
				 nextletter="a";

			try {
				ps = con.prepareStatement("INSERT INTO classesgrades VALUES(?,?);");
			
				ps.setString(1, grade);
				ps.setString(2, nextletter);
				ps.executeUpdate();
				
				try {
					ps = con.prepareStatement("INSERT INTO classes VALUES(?);");
				
					ps.setString(1, grade+nextletter);
					
					ps.executeUpdate();
					
				}
				
				catch (SQLException ex){
					System.out.println("\n in CreateNewClass error ");
					return ex;
				}	
			
			}
			
			catch (SQLException ex){
				System.out.println("\n in CreateNewClass error ");
				return ex;
			}
			result = new Vector<String[]>();
			String[] identity=new String[numOfCols];
			identity[0]="CreateNewClass";
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;
		}
		return result;
		
	}
	
	public Object CreateNewSemester()
	{
		ResultSet rs=null;
		Vector<String[]> result = new Vector<String[]>();
		PreparedStatement ps=null;
		

		try {
			ps = con.prepareStatement("SELECT Year,Semester FROM semesters WHERE Current=true;;");
		
			rs = ps.executeQuery();
			
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String Year=result.get(0)[0];
			String Semester=result.get(0)[1];
			
			if(Semester.equalsIgnoreCase("B"))
			{
			
				Integer temp=Integer.parseInt(Year);
				temp++;
				Year=temp.toString();
				Semester="A";
			}
			else
			{
				Semester="B";
			}
					try {
						ps = con.prepareStatement("UPDATE semesters SET Current=false WHERE Current=true;");
					
						ps.executeUpdate();
						
						
			
			
					}
			catch (SQLException ex){
				System.out.println("\n in CreateNewSemester error ");
				return ex;
			}
					try {
						ps = con.prepareStatement("INSERT INTO semesters(Year,Semester,Current) VALUES(?,?,true);");
					
						ps.setString(1, Year);
						ps.setString(2, Semester);
						ps.executeUpdate();
						
						
			
			
					}
			catch (SQLException ex){
				System.out.println("\n in CreateNewSemester error ");
				return ex;
			}
			
			result = new Vector<String[]>();
			String[] identity=new String[numOfCols];
			identity[0]="CreateNewSemester";
			result.addElement(identity);
		
			
			System.out.println("\n done with sql ");
			
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CreateNewSemester error ");
			return ex;
		}
		
		return result;
		
	}
	public Object CourseToStudentQuery(String[] msg)
	
	{
		ResultSet rs=null;
		
		PreparedStatement ps=null;
		
		
		
		try {
			ps = con.prepareStatement("SELECT Cname, Cid, Semester, Year, Grade,IsExeptional FROM studentcourses WHERE sid=?;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.setString(1, msg[1]);
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			String[] headlines=new String[numOfCols];
			for (int j=1; j<=numOfCols; j++)
				headlines[j-1] = rsmd.getColumnName(j);
			result.addElement(headlines);
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			identity[0]=msg[1];
			result.addElement(identity);
			identity=new String[numOfCols];
			if(msg[0].startsWith("ForParent")) identity[0]="ForParentCoursesOfStudent";
			else if (msg[0].startsWith("ForPrinciple"))identity[0]="ForPrincipleCoursesOfStudent";
			else if (msg[0].startsWith("ForAdmin"))identity[0]="ForAdminCoursesOfStudent";
			else identity[0]="CoursesOfStudent";
			result.addElement(identity);
			
		//	CoursesResult.lOfCourses=new Vector<Course>();
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in CourseToStudentQuery error ");
			return ex;
		}
		
	}  // end of selectQuery
	
	public Object IdsForPrinciple()
	{
ResultSet rs=null;
		
		PreparedStatement ps=null;
		

		try {
			
			ps=con.prepareStatement("SELECT UserID  FROM userdb WHERE UserType=?;");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			ps.setString(1, "Student");
			rs = ps.executeQuery();
			
			ResultSetMetaData rsmd = rs.getMetaData();
			int numOfCols = rsmd.getColumnCount();
			
			Vector<String[]> result = new Vector<String[]>();
			
			String[] headlines=new String[numOfCols];
			for (int j=1; j<=numOfCols; j++)
				headlines[j-1] = rsmd.getColumnName(j);
			result.addElement(headlines);
			
			while (rs.next()){
				String[] tuple=new String[numOfCols];
				for (int i=1 ; i<=numOfCols ; i++)
					tuple[i-1] = rs.getString(i);
				result.addElement(tuple);	
			}
			rs.close();
			String[] identity=new String[numOfCols];
			identity[0]="IdsForPrinciple";
			result.addElement(identity);
		//	CoursesResult.lOfCourses=new Vector<Course>();
			
			System.out.println("\n done with sql ");
			return result;
		
		}
		
		catch (SQLException ex){
			System.out.println("\n in IdsForPrincipleQuery error ");
			return ex;
		}
	}

	/**
	 *  User login and sql query for user search 
	 * 
	 * @param msg from client 
	 * @return Boolean - is the connection successful or not
	 */
	
		
	public String[] login (String[] msg)
	{
		ResultSet rs=null;
		String password="a";
		String connsts="a";
		String utype="a";
		String id="a";
		String[] result= new String[4] ;
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT * FROM userdb WHERE UserID=?;");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			
		}
		 
			try {
				ps.setString(1, msg[1]);
				rs=ps.executeQuery();
				
					while(rs.next()) 
					{
					
					id=rs.getString(1);
					password=rs.getString(4);
					connsts=rs.getString(5);
					utype=rs.getString(6);
					
					}
				rs.close();
				System.out.println("\n1  login ok sql");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			result[0]=id;//0-id,1-connection status,2-user type, 3-login status(ok\failed)
			result[1]=connsts;
			result[2]=utype;
			if(password.equalsIgnoreCase("a")){System.out.println("\n  from sql: no such user"); result[3]="FAILED"; return result;}
			if (connsts.equalsIgnoreCase("0"))
			{
				if (password.equalsIgnoreCase(msg[2])) { 
				update(msg,"1");//
				System.out.println("\n login ok sql");
				result[3]="OK";
				return result;}
				else{result[3]="FAILED";  System.out.println("\n login failed 1 sql");return result ;}
			}
			else {result[3]="FAILED";System.out.println("\n login failed 2 sql"); return result ;}
	}
	
	/**
	 *  User logout and sql query for user search 
	 * 
	 * @param msg from client 
	 * @return Boolean - is the connection successful or not
	 */
	
		
	public void logout(String[] msg)
	{
		ResultSet rs=null;
	
		String connsts=null;
		PreparedStatement ps=null;
		try {
			ps = con.prepareStatement("SELECT * FROM userdb WHERE UserID=?;");
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
			
		}
	try {
		ps.setString(1, msg[1]);
		rs=ps.executeQuery();
		
			while(rs.next()) 
			{
			connsts=rs.getString(5);
			}
		rs.close();

	} catch (SQLException e1) {
		// TODO Auto-generated catch block
		e1.printStackTrace();
	}
	   
		if (connsts.equalsIgnoreCase("1"))
		{
			update(msg,"0");
			
		}
		
	
	
	}
	
	
	
	
	public void update(String[] msg,String newSt)
	{
		
		PreparedStatement ps=null;
		
		try {
			ps=con.prepareStatement("UPDATE userdb SET IsConnected=? WHERE UserID=?;");
			ps.setString(1,newSt);
			ps.setString(2, msg[1]);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	/////////////////////////////////////////////////////////////////////////////
	// insert to assignmentsubmission 
	public void tnewtask(String deadLine,String calssID,String cid,String taskNum,String filePath){
		PreparedStatement ps=null;
		FileInputStream fin = null;
		try {
			//Getting the file
			File stusub= new File(filePath);
			fin = new FileInputStream(stusub);
			byte [] mybytearr =new byte [(int) stusub.length()] ;
			System.out.println("mybyteARr Lenght is : "+mybytearr.length);
			//	mybytearr = fin.read();
			
			System.out.println("after file	fin");
			int lenght = (int) stusub.length();
			System.out.println("mysql : "+stusub.length()+"\n" + stusub.getName()+"\n" + stusub.getAbsolutePath()+"lenght : "+lenght);
			//make the date of sub
//			Calendar cal = new GregorianCalendar();
//	        int month = cal.get(Calendar.MONTH);
//	    	month++;
//	        int year = cal.get(Calendar.YEAR);
//	        int day = cal.get(Calendar.DAY_OF_MONTH);
//	        int hour = cal.get(Calendar.HOUR_OF_DAY);
//	        int min = cal.get(Calendar.MINUTE);
//	        int sec = cal.get(Calendar.SECOND);
//	          	 
//	        String date = year+":"+month+":"+day+":"+hour+":"+min+":"+sec;  				//making the string of time and date for the DB
	       	//making the quary

				ps=con.prepareStatement("INSERT INTO definenewassignment VALUES(?,?,?,?,?);");

		
			ps.setString(1, calssID);
			ps.setString(2, cid);
			ps.setString(3, taskNum);
			ps.setString(4, deadLine);
     		//puting the file in the blob
			ps.setBinaryStream(5, fin, (int) mybytearr.length);
			//ps.setObject(4,mybytearr);
			ps.executeUpdate();
			System.out.println("after executeUpdate");
					}catch (Exception e) {
			System.out.println("error insert to db");
		}	
	}
	
	public void submitAss(String userID,String cid,String taskNum,String filePath,int flag){
		PreparedStatement ps=null;
		FileInputStream fin = null;
		try {
			//Getting the file
			File stusub= new File(filePath);
			fin = new FileInputStream(stusub);
			byte [] mybytearr =new byte [(int) stusub.length()] ;
			System.out.println("mybyteARr Lenght is : "+mybytearr.length);
			//	mybytearr = fin.read();
			
			System.out.println("after file	fin");
			int lenght = (int) stusub.length();
			System.out.println("mysql : "+stusub.length()+"\n" + stusub.getName()+"\n" + stusub.getAbsolutePath()+"lenght : "+lenght);
			//make the date of sub
			Calendar cal = new GregorianCalendar();
	        int month = cal.get(Calendar.MONTH);
	    	month++;
	        int year = cal.get(Calendar.YEAR);
	        int day = cal.get(Calendar.DAY_OF_MONTH);
	        int hour = cal.get(Calendar.HOUR_OF_DAY);
	        int min = cal.get(Calendar.MINUTE);
	        int sec = cal.get(Calendar.SECOND);
	          	 
	        String date = year+":"+month+":"+day+":"+hour+":"+min+":"+sec;  				//making the string of time and date for the DB
	       	//making the quary
			if (flag==1)
				ps=con.prepareStatement("INSERT INTO assignmentsubmission VALUES(?,?,?,?,?);");
			else if (flag==2){
				ps=con.prepareStatement("INSERT INTO definenewassignment VALUES(?,?,?,?,?);");
			}
			else if (flag==3){
				ps=con.prepareStatement("INSERT INTO evaluationform VALUES(?,?,?,?,?);");
			}
			ps.setString(1, userID);
			ps.setString(2, cid);
			ps.setString(3, taskNum);
			ps.setString(4, date);
     		//puting the file in the blob
			ps.setBinaryStream(5, fin, (int) mybytearr.length);
			//ps.setObject(4,mybytearr);
			ps.executeUpdate();
			System.out.println("after executeUpdate");
					}catch (Exception e) {
			System.out.println("error insert to db");
		}	
	}
	
	public void submitEvaluationForm(String userID,String cid,String filePath,int flag){
		PreparedStatement ps=null;
		FileInputStream fin = null;
		try {
			//Getting the file
			File stusub= new File(filePath);
			fin = new FileInputStream(stusub);
			byte [] mybytearr =new byte [(int) stusub.length()] ;
			System.out.println("mybyteARr Lenght is : "+mybytearr.length);
			//	mybytearr = fin.read();
			
			System.out.println("after file	fin");
			int lenght = (int) stusub.length();
			System.out.println("mysql : "+stusub.length()+"\n" + stusub.getName()+"\n" + stusub.getAbsolutePath()+"lenght : "+lenght);
			//make the date of sub
			Calendar cal = new GregorianCalendar();
	        int month = cal.get(Calendar.MONTH);
	    	month++;
	        int year = cal.get(Calendar.YEAR);
	        int day = cal.get(Calendar.DAY_OF_MONTH);
	        int hour = cal.get(Calendar.HOUR_OF_DAY);
	        int min = cal.get(Calendar.MINUTE);
	        int sec = cal.get(Calendar.SECOND);
	          	 
	        String date = year+":"+month+":"+day+":"+hour+":"+min+":"+sec;  				//making the string of time and date for the DB
	       	//making the quary
			if (flag==1)
				ps=con.prepareStatement("INSERT INTO assignmentsubmission VALUES(?,?,?,?,?);");
			else if (flag==2){
				ps=con.prepareStatement("INSERT INTO definenewassignment VALUES(?,?,?,?,?);");
			}
			else if (flag==3){
				ps=con.prepareStatement("INSERT INTO evaluationform VALUES(?,?,?,?);");
			}
			ps.setString(1, userID);
			ps.setString(2, cid);
			ps.setString(3, date);
     		//puting the file in the blob
			ps.setBinaryStream(4, fin, (int) mybytearr.length);
			//ps.setObject(4,mybytearr);
			ps.executeUpdate();
			System.out.println("after executeUpdate");
					}catch (Exception e) {
			System.out.println("error insert to db");
		}	
	}
	
	
	
	//getting the ass
	public void getAss(String userID,String cID){
			ResultSet rs = null;
			PreparedStatement stmt;   
			FileInputStream fin = null;
			PreparedStatement ps = null;
			FileOutputStream fos = null;
	   	   File f = new File("D:\\s\\av.mp3");
	   	
try{
	        stmt = con.prepareStatement("SELECT File FROM evaluationform  WHERE sid = "+userID+"AND cid="+cID);
	        rs = stmt.executeQuery();
	        if (!rs.next()) {
	          System.out.println("No such file stored.");
	        } else {
	        	
	        	byte [] mybytearr;
	        	mybytearr = rs.getBytes(1);
	        	OutputStream getfile=  new FileOutputStream("D:\\s\\avv.mp3");	   
	        	getfile.write(mybytearr);
	        	getfile.close();
	        	System.out.println("The File Is Sent ");
	            }
	        
}
catch (Exception e) {
				System.out.println("Error geting the blob");
	        }
			}
	        
		public HashMap<String, Object>  viewNewAssignmentGetDetal(String classId,String cID){
			HashMap<String, Object> param = new HashMap<String, Object>();
			ResultSet rs = null;
			PreparedStatement stmt;   
			String[] returnString=new String [3];
			System.out.println("\nIN MYSQL classId is : "+classId+"\ncID : "+cID+"\n");
			try{
			stmt= con.prepareStatement("SELECT TaskNum,DeadLine,myFile FROM definenewassignment d, studentdb s, studentcourses sc WHERE  d.ClassID=s.ClassID AND sc.Cid=d.Cid GROUP BY TaskNum;");
			rs=stmt.executeQuery();
				while (rs.next()){
				String taskNum = rs.getString(1);
				System.out.println("IN MYSQL taskNum is : "+taskNum);
				String deadLine=rs.getString(2);
				System.out.println("IN MYSQL Date is : "+deadLine);
				param.put("funcName", "viewNewAssignmentGetDetal");
				param.put("taskNum", taskNum);
				param.put("deadLine", deadLine);
//				
//				returnString[0]="viewNewAssignmentGetDetal";
//				returnString[1]=taskNum;
//				returnString[2]=deadLine;
			//	System.out.println("IN MYSQL Date is : "+deadLine+"\nreturnString  : "+returnString);

				//getting the file
				byte [] myByteArr;
				myByteArr = rs.getBytes(3);
	        	param.put("mybytearr", myByteArr);

	        
	        	System.out.println("The File Is Sent ");
				return(param);
				}
//			}
			}catch (Exception e) {
				System.out.println("IN MYSQL viewNewAssignmen ERRRRRROOORRR");
			}
			return null;			
		}
			
		public byte []  viewNewAssignmentGetFile(String classId,String cID){
			ResultSet rs = null;
			PreparedStatement stmt;   
			try{
			stmt= con.prepareStatement("SELECT myFile FROM definenewassignment d, studentdb s, studentcourses sc WHERE  d.ClassID=s.ClassID AND sc.Cid=d.Cid GROUP BY TaskNum;");
			rs=stmt.executeQuery();

				while (rs.next()){
				System.out.println("IN MYSQL viewNewAssignmentGetFile");
				//getting the file
				byte [] mybytearr;
	        	mybytearr = rs.getBytes(1);
	        	
//	        	OutputStream getfile=  new FileOutputStream("D:\\s\\avv.mp3");	   
//	        	getfile.write(mybytearr);
	        	System.out.println(mybytearr.length);
//	        	getfile.close();
	        	System.out.println("The mybytearr Is Sent ");
	        	return(mybytearr);
				}
//			}
			}catch (Exception e) {
				System.out.println("IN MYSQL viewNewAssignmen ERRRRRROOORRR");
			}
			return null;			
		}
		

	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	public static void main(String[] args) 
	{
		ResultSet rs;
		Statement st;
		//String[] test=new String[6];
		Vector<String[]> CoursesResult=new Vector<String[]>();
		mysqlConnection conn = null;
		try 
		{
           Class.forName("com.mysql.jdbc.Driver").newInstance();
       } catch (Exception ex) {/* handle the error*/}
		
	
	
	
		try {
			conn=new mysqlConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//<<<<<<< .mine
	//	conn.submitAss();
//		conn.getAss();
//<<<<<<< .mine
//		String a = conn.viewNewAssignment("12a","02102");
//	test[0]="blocked";
//	test[1]="notblocked";
//	test[2]="true";
//=======
	//test[0]="SetDesExReqReplaceTeacher";
	//test[1]="02101";
	//test[2]="108";
	//test[3]="109";
	//test[4]="121";
	//test[5]="12a";
	String temp="CreateNewClass 2";
	String[] test=temp.split(" ");
	char c='a';
	c++;
//>>>>>>> .r209
//	test[3]="120";
//	test[4]="7a";
//	test=conn.YearQuery(test);
//	//System.out.println("\n"+test[0]+" "+test[1]  );
//	
CoursesResult=(Vector<String[]>)conn.CreateNewClass(test);
	System.out.println("\n"+test[0]+" "+test[1]  );
//	//student=new StudentClass("Yossi","Biton","106","1234");
//	String pass="1234";
//	String cs="0";
//	String type="Student";
//	String sid="106";
//=======
	//test[0]="ForParentCoursesOfStudent";
	//test[1]="120";
	//test[2]="7a";
	//test[3]="02101";
	//test[4]="121";
	//test=conn.YearQuery(test);
	//System.out.println("\n"+test[0]+" "+test[1]  );
	
	//CoursesResult=(Vector<String[]>)conn.ExReqStudRemQuery();
//	System.out.println("\n"+test[0]+" "+test[1]  );
	//student=new StudentClass("Yossi","Biton","106","1234");
//	String pass="1234";
//	String cs="0";
//	String type="Student";
//	String sid="106";
//>>>>>>> .r180
//	conn.insert("insert into userdb values("+sid+","+student.FirstName+","+student.LastName+","+pass+","+cs+","+type+");");
	
	
	/*try {
		ps.setString(1, sid);
		ps.setString(2, "Yossi");
		ps.setString(3, "Biton");
		ps.setString(4, pass);
		ps.setString(5, cs);
		ps.setString(6, type);
		ps.executeUpdate();
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}*/
	
// String toinsert="insert"+" "+sid+" "+"Yossi"+" "+"Bitton"+" "+pass+" "+cs+" "+type;
// conn.insertUser(toinsert);
	
}	
	}
	
	

	
	



